using System;
using Gtk;
using System.IO;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Collections.Generic;
using System.Text;
using LumenWorks.Framework.IO.Csv;



	
namespace estiglp11126197csharp
{
	class MainClass
	{
		private SQLiteConnection sql_con;
		private SQLiteCommand sql_cmd;
		public static void Main (string[] args)
		{
			Console.WriteLine ("Base dados");
			
			Console.WriteLine ("Base dados");
			SQLiteConnection sql_con = new SQLiteConnection(" Data Source = BaseDados.db");
			sql_con.Open();
			SQLiteTransaction trasition = sql_con.BeginTransaction();
			SQLiteCommand sql_cmd = sql_con.CreateCommand();			
			sql_cmd.CommandText = "DROP TABLE IF EXISTS Rebides_information";
			sql_cmd.ExecuteNonQuery();
			sql_cmd.CommandText = " CREATE TABLE IF NOT EXISTS Rebides_information(professor_codigo integer,  nome text, grau text,"+ 
																				   "curso text, categoria text, regimetempo text,"+
					                                                               "anolectivo integer, tipoestabelecimento text,"+
					                                                               "nomeestabelecimento text)";
			sql_cmd.ExecuteNonQuery();		
			
			Console.WriteLine("Base dados created.");
			trasition.Commit();     
            sql_con.Close();
                    
                       
                       
            Console.WriteLine("loading Base dados...");
               for (int i = 0; i < 10; i++)            {
                                //nome ficheiro csv//
                                String filename = "reb" + i + ".csv";
                                Console.WriteLine(filename);
                                //abrir ficheiros csv
                CsvReader fileReader = new CsvReader(new StreamReader(filename), true);
                                //query
                sql_cmd.CommandText = "INSERT INTO DOCENTES(professor_codigo integer, nome text, grau text"+ 
										                   "curso text, categoria text, regime tempo text"+
					                                       "ano lectivo integer, tipo estabelecimento text"+
					                                       "nome estabelecimento text)" +
                                                           "VALUES(?,?,?,?,?,?,?,?,?)";
                               
                                for(int x = 0; i < 9; i++)
                                {
                                       sql_cmd.Parameters.Add(sql_cmd.CreateParameter());
                                }


                while (fileReader.ReadNextRecord())
                {
                    for (int j = 0; j < 9; j++)
                    {
                        sql_cmd.Parameters[j].Value = fileReader[j];
                    }
                    try{
						sql_cmd.ExecuteNonQuery();
						
					}
					catch(System.InvalidOperationException exc)
					{
						
					}
					
                }
            }                  
                       
            trasition.Commit();
            sql_con.Close();
            Console.WriteLine("Base dados loaded.");      
		}
	}
}
			
